﻿using CCWin;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using TelephoneYiShou;

namespace Commodity
{
    public partial class Commodityselect : Skin_Mac
    {
        public Commodityselect()
        {
            InitializeComponent();
        }
        public DataTable dt;
        int i, start;//i表示总行数，start表示当前位置
        int size = 10;//size表示一页显示的数量
        string str1 = "select a.CommodityID,a.CommodityName,b.TypeName,a.OrderGoodsPrice,a.SellGoodsPrice,a.Colour,c.SupplierName,a.introduce from Commodity a join CommodityType b on a.TypeID=b.TypeID join Supplier c on a.SupplierID=c.SupplierID";
        string str = "server=.;database = TelephoneYiShou;uid = sa;pwd = 123456";
        public void show(int i, int j)
        {
            SqlConnection sqlcon = new SqlConnection(str);
            sqlcon.Open();
            SqlCommand sqlcmd = new SqlCommand(str1, sqlcon);
            SqlDataAdapter sqlada = new SqlDataAdapter(sqlcmd);
            DataSet ds = new DataSet();
            sqlada.Fill(ds, i, j, "[Commodity]");//把数据库表中的i-j的记录数用来填充数据集
            Commoditydgv.DataSource = ds.Tables["[Commodity]"];
            ds = null;//清空数据集
        }
        private void Commodityselect_Load(object sender, EventArgs e)
        {
            DataTable dt = DBHelper.GetDataTable(str1);
            i = dt.Rows.Count;//获取数据的条数
            show(0, size);
            plb.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
            //商品类型下拉框绑定     
            string sqltp = "select distinct TypeName from commodityType";
            DataTable dte = DBHelper.GetDataTable(sqltp);
            for (int i = 0; i < dte.Rows.Count; i++)
            {
                tptxt.Items.Add(dte.Rows[i][0]);
            }
            //供应厂商下拉框绑定     
            string sqlsup = "select distinct SupplierName from Supplier";
            DataTable dsup = DBHelper.GetDataTable(sqlsup);
            for (int i = 0; i < dsup.Rows.Count; i++)
            {
                suptxt.Items.Add(dsup.Rows[i][0]);
            }
        }


        private void updatetxt_Click(object sender, EventArgs e)
        {
            DataTable dt = DBHelper.GetDataTable(str1);
            i = dt.Rows.Count;//获取数据的条数
            show(0, size);
            plb.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
            this.CommodityIDtxt.Text = "";
            this.CommodityNametxt.Text = "";
        }

        private void selectbtn_Click(object sender, EventArgs e)
        {
            start = 0;//初始化页数
            string cid = CommodityIDtxt.Text;
            string cname = CommodityNametxt.Text;
            if (cid!= ""&&cname=="") {
                string sql = string.Format("select a.CommodityID,a.CommodityName,b.TypeName,a.OrderGoodsPrice,a.SellGoodsPrice,a.Colour,c.SupplierName,a.introduce from Commodity a join CommodityType b on a.TypeID=b.TypeID join Supplier c on a.SupplierID=c.SupplierID where a.CommodityID={0}", Convert.ToInt32(cid));
                DataTable dt = DBHelper.GetDataTable(sql);
                i = dt.Rows.Count;//获取数据的条数
                if (i == 0)
                {
                    MessageBoxEx.Show("没有" + cid + "的订单");
                }
                else
                {
                    show(0, size);
                    plb.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
                }
                Commoditydgv.DataSource = dt;
            }
            else if (cid == "" && cname != "")
            {
                string sql = string.Format("select a.CommodityID,a.CommodityName,b.TypeName,a.OrderGoodsPrice,a.SellGoodsPrice,a.Colour,c.SupplierName,a.introduce from Commodity a join CommodityType b on a.TypeID=b.TypeID join Supplier c on a.SupplierID=c.SupplierID where a.CommodityName like'%{0}%'", cname);
                    DataTable dt = DBHelper.GetDataTable(sql);
                i = dt.Rows.Count;//获取数据的条数
                if (i == 0)
                {
                    MessageBoxEx.Show("没有" + cname + "的订单");
                }
                else
                {
                    show(0, size);
                    plb.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
                }
                Commoditydgv.DataSource = dt;
            }
            else if (cid != "" && cname != "")
            {
                string sql = string.Format("select a.CommodityID,a.CommodityName,b.TypeName,a.OrderGoodsPrice,a.SellGoodsPrice,a.Colour,c.SupplierName,a.introduce from Commodity a join CommodityType b on a.TypeID=b.TypeID join Supplier c on a.SupplierID=c.SupplierID where a.CommodityID='{0}' and a.CommodityName like'%{1}%'", Convert.ToInt32(cid),cname);
                DataTable dt = DBHelper.GetDataTable(sql);
                i = dt.Rows.Count;//获取数据的条数
                if (i == 0)
                {
                    MessageBoxEx.Show("没有该订单");
                }
                else
                {
                    show(0, size);
                    plb.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
                }
                Commoditydgv.DataSource = dt;
            }
            else if (cid == "" && cname == "")
            {
                MessageBoxEx.Show("请输入关键字");
            }
        }

        private void deletebtn_Click(object sender, EventArgs e)
        {
            string id = idtxt.Text;
            string nm = nmtxt.Text;
            string tp = tptxt.Text;
            string inp = inptxt.Text;
            string outp = outptxt.Text;
            string cl = cltxt.Text;
            string big = bigtxt.Text;
            string sup = suptxt.Text;
            if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(nm) ||
                string.IsNullOrEmpty(tp) || string.IsNullOrEmpty(inp) || string.IsNullOrEmpty(outp)
                || string.IsNullOrEmpty(cl) || string.IsNullOrEmpty(big) || string.IsNullOrEmpty(sup))
            {
                MessageBoxEx.Show("信息输入不完整！");
                return;//直接返回
            }
            string sql = string.Format("delete from Commodity where CommodityID={0}", Convert.ToInt32(id));
            DialogResult result = MessageBoxEx.Show("确认删除？", "温馨提示", MessageBoxButtons.OKCancel);
            if (result == DialogResult.OK)
            {
                try
                {
                    if (DBHelper.ExecuteNonQuery(sql))
                    {
                        MessageBoxEx.Show("删除成功！");
                        DataTable dt = DBHelper.GetDataTable(str1);
                        i = dt.Rows.Count;//获取数据的条数
                        show(0, size);
                        plb.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start+1, i / size + 1, i);//显示数据
                    }
                    else
                    {
                        MessageBoxEx.Show("删除失败,请联系管理员!");
                    }
                }
                catch ( Exception)
                {
                    MessageBoxEx.Show("请先删除库存中商品的");
                }
            }
            else
            {
                MessageBoxEx.Show("您选择了取消删除！");
            }
        }

        private void Commoditydgv_Click(object sender, EventArgs e)
        {
            idtxt.Text = Commoditydgv.Rows[Commoditydgv.CurrentRow.Index].Cells[0].Value.ToString();
            nmtxt.Text = Commoditydgv.Rows[Commoditydgv.CurrentRow.Index].Cells[1].Value.ToString();
            tptxt.Text = Commoditydgv.Rows[Commoditydgv.CurrentRow.Index].Cells[2].Value.ToString();
            inptxt.Text = Commoditydgv.Rows[Commoditydgv.CurrentRow.Index].Cells[3].Value.ToString();
            outptxt.Text = Commoditydgv.Rows[Commoditydgv.CurrentRow.Index].Cells[4].Value.ToString();
            cltxt.Text = Commoditydgv.Rows[Commoditydgv.CurrentRow.Index].Cells[5].Value.ToString();
            suptxt.Text = Commoditydgv.Rows[Commoditydgv.CurrentRow.Index].Cells[6].Value.ToString();
            bigtxt.Text = Commoditydgv.Rows[Commoditydgv.CurrentRow.Index].Cells[7].Value.ToString();
        }

        private void changebtn_Click(object sender, EventArgs e)
        {
            string id = idtxt.Text;
            string nm = nmtxt.Text;
            string tp = tptxt.Text;
            string tpid = null;
            string inp = inptxt.Text;
            string outp = outptxt.Text;
            string cl = cltxt.Text;
            string big = bigtxt.Text;
            string sup = suptxt.Text;
            string supid = null;
            if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(nm) ||
                string.IsNullOrEmpty(tp) || string.IsNullOrEmpty(inp) || string.IsNullOrEmpty(outp)
                || string.IsNullOrEmpty(cl) || string.IsNullOrEmpty(big)||string.IsNullOrEmpty(sup))
            {
                MessageBoxEx.Show("信息输入不完整！");
                return;//直接返回
            }
            //获取商品类型编号
            if (!string.IsNullOrEmpty(tp))
            {
                string tpsql = string.Format("select TypeID from CommodityType where TypeName='{0}'", tp);
                tpid = DBHelper.GetDataTable(tpsql).Rows[0][0].ToString();
            }
            //获取供应商
            if (!string.IsNullOrEmpty(sup))
            {
                string supsql = string.Format("select SupplierID from Supplier where SupplierName='{0}'", sup);
                supid = DBHelper.GetDataTable(supsql).Rows[0][0].ToString();
            }
            string sql = string.Format("update Commodity set CommodityName='{0}',TypeID='{1}',OrderGoodsPrice='{2}',SellGoodsPrice='{3}',introduce='{4}',SupplierID='{5}',Colour='{6}' where CommodityID='{7}'", nm,tpid, Convert.ToDecimal(inp), Convert.ToDecimal(outp), big,supid,cl, Convert.ToInt32(id));
            if (DBHelper.ExecuteNonQuery(sql))
            {
                MessageBoxEx.Show("商品信息修改成功！");
                DataTable dt = DBHelper.GetDataTable(str1);
                i = dt.Rows.Count;//获取数据的条数
                show(0, size);
                plb.Text = string.Format("第1页/共{0}页 共{1}条数据", i / size + 1, i);//显示数据
            }
            else
            {
                MessageBoxEx.Show("修改失败,请联系管理员!");
            }
        }

        private void exitbtn_Click(object sender, EventArgs e)
        {
            this.Close();
        }



        private void label11_Click(object sender, EventArgs e)
        {

        }
       
        //上一页
        private void backbt_Click(object sender, EventArgs e)
        {
            if (start < 1)
            {
                MessageBoxEx.Show("已是第一页......");
            }
            else
            {
                start = start - 1;
                show(start * size, size);
            }
            plb.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
        }
        //下一页
        private void nextbt_Click(object sender, EventArgs e)
        {
            if (start + 1 > i / size)
            {
                MessageBoxEx.Show("已是最后页");
            }
            else
            {
                start = start + 1;
                show(start * size, size);
            }
            plb.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
        }
        

        private void cltxt_MouseEnter(object sender, EventArgs e)
        {
            //颜色数据绑定
            cltxt.Items.Clear();
            string sqlcolor = string.Format("select colour from commodity where commodityname = '{0}'", nmtxt.Text);
            DataTable dtes = DBHelper.GetDataTable(sqlcolor);
            for (int i = 0; i < dtes.Rows.Count; i++)
            {
                cltxt.Items.Add(dtes.Rows[i][0]);
            }
        }

        private void idtxt_TextChanged(object sender, EventArgs e)
        {

        }
    }
}
